Banco de Dados

Aula 07 - SQL III: JOINs e Agregações

Helder Jefferson Ferreira da Luz

helder.luz@ifpr.edu.br

Objetivos da aula

• Aprender a combinar dados de múltiplas tabelas.
• Conhecer as funções de agregação para resumir e calcular dados.
• Ver as funções de agrupamento de linhas, para utilizar em conjunto com as funções de agregação.

Antes de iniciar

Adicione novos registros ao banco de dados cliente

INSERT INTO pedido (status, id_cliente) VALUES
('Pago', 3),
('Enviado', 5),
('Pendente', 1),
('Entregue', 7),
('Pago', 10),
('Pendente', 2),
('Enviado', 8),
('Pago', 4),
('Entregue', 12),
('Pendente', 3),
('Pago', 15),
('Enviado', 9),
('Pendente', 5),
('Pago', 18),
('Enviado', 20),
('Pendente', 14),
('Pago', 6),
('Enviado', 1),
('Enviado', 11),
('Pago', 7);

INSERT INTO item_pedido VALUES
-- Pedido 21 (2 produtos)
(21, 1, 1, 3500.00),
(21, 2, 1, 80.00),
-- Pedido 22 (1 produto)
(22, 10, 1, 1200.00),
-- Pedido 23 (3 produtos)
(23, 3, 1, 2200.00),
(23, 11, 1, 320.00),
(23, 19, 2, 45.00),
-- Pedido 24 (1 produto)
(24, 7, 1, 899.99),
-- Pedido 25 (2 produtos)
(25, 8, 1, 499.90),
(25, 9, 1, 350.00),
-- Pedido 26 (1 produto)
(26, 4, 1, 59.90),
-- Pedido 27 (2 produtos)
(27, 5, 1, 450.00),
(27, 14, 1, 120.00),
-- Pedido 28 (3 produtos)
(28, 6, 1, 2600.00),
(28, 16, 1, 250.00),
(28, 20, 1, 69.90),
-- Pedido 29 (1 produto)
(29, 12, 1, 79.90),
-- Pedido 30 (2 produtos)
(30, 13, 3, 50.00),
(30, 3, 1, 2200.00),
-- Pedido 31 (1 produto)
(31, 18, 1, 120.00),
-- Pedido 32 (2 produtos)
(32, 9, 1, 350.00),
(32, 2, 1, 80.00),
-- Pedido 33 (1 produto)
(33, 1, 1, 3500.00),
-- Pedido 34 (2 produtos)
(34, 17, 1, 899.00),
(34, 15, 1, 89.90),
-- Pedido 35 (3 produtos)
(35, 10, 1, 1200.00),
(35, 11, 1, 320.00),
(35, 18, 1, 120.00),
-- Pedido 36 (1 produto)
(36, 4, 1, 59.90),
-- Pedido 37 (2 produtos)
(37, 8, 1, 499.90),
(37, 19, 1, 45.00),
-- Pedido 38 (3 produtos)
(38, 6, 1, 2600.00),
(38, 20, 1, 69.90),
(38, 14, 1, 120.00),
-- Pedido 39 (1 produto)
(39, 7, 1, 899.99),
-- Pedido 40 (2 produtos)
(40, 3, 1, 2200.00),
(40, 12, 2, 79.90);

O Problema: Dados Espalhados

Até agora, nossas consultas foram em uma tabela por vez.

-- Quem fez o pedido? (só temos o id do cliente)
SELECT id, data_pedido, cliente_id FROM pedido;

-- Qual o nome do cliente? (precisamos de outra consulta)
SELECT nome FROM cliente WHERE id = ?;

Isso é ineficiente e impraticável. Precisamos de uma forma de "juntar" as tabelas cliente e pedido em uma única consulta. A solução é o JOIN.

INNER JOIN

O INNER JOIN combina linhas de duas ou mais tabelas com base em uma coluna relacionada entre elas. Ele retorna apenas os registros que têm valores correspondentes em ambas as tabelas.

INNER JOIN

Sintaxe:

SELECT t1.coluna, t2.coluna
FROM tabela1 AS t1
INNER JOIN tabela2 AS t2 ON t1.coluna_fk = t2.coluna_pk;

O uso de aliases (apelidos como t1, t2) é uma boa prática para legibilidade.

Sem apelidos.

SELECT tabela1.coluna, tabela2.coluna
FROM tabela1
INNER JOIN tabela2 ON tabela1.coluna_fk = tabela2.coluna_pk;

Sintaxe do JOIN

SELECT t1.coluna, t2.coluna
FROM tabela1 AS t1
INNER JOIN tabela2 AS t2 ON t1.coluna_fk = t2.coluna_pk;

• INNER JOIN: Especifica que quer juntar a tabela1 (t1) com a tabela2 (t2).
• ON: Esta é a condição de junção. Ela diz ao banco de dados como as tabelas se relacionam: "combine as linhas onde o valor da coluna_fk da tabela1 é igual ao valor da coluna_pk da tabela2".

INNER JOIN - Exemplo

Pergunta: Qual o nome do cliente de cada pedido?

SELECT
    p.id AS id_pedido,
    p.data_pedido,
    c.nome AS nome_cliente
FROM
    pedido AS p
INNER JOIN
    cliente AS c ON p.id_cliente = c.id;

Resultado: Uma tabela virtual que combina as informações, mostrando o ID do pedido, a data e o nome do cliente correspondente. Clientes que nunca fizeram um pedido não aparecerão.

LEFT JOIN

O LEFT JOIN retorna todos os registros da tabela da esquerda (tabela1) e os registros correspondentes da tabela da direita (tabela2). Se não houver correspondência, as colunas da tabela da direita terão o valor NULL.

LEFT JOIN

Pergunta: Liste todos os clientes e, se tiverem feito pedidos, mostre os IDs desses pedidos.

SELECT
    c.nome AS nome_cliente,
    p.id AS id_pedido
FROM
    cliente AS c
LEFT JOIN
    pedido AS p ON c.id = p.id_cliente;

Resultado: Todos os clientes serão listados. Aqueles que não têm pedidos terão NULL na coluna id_pedido.

RIGHT JOIN

O RIGHT JOIN é o oposto do LEFT JOIN. Ele retorna todos os registros da tabela da direita (tabela2) e os correspondentes da tabela da esquerda (tabela1). Se não houver correspondência, as colunas da tabela da esquerda terão o valor NULL.

Na prática, o RIGHT JOIN é raramente usado. Quase sempre é possível reescrever a consulta usando um LEFT JOIN, que é mais intuitivo de ler.

RIGHT JOIN

Exemplo (equivalente ao LEFT JOIN anterior):

SELECT
    cliente.nome AS nome_cliente,
    pedido.id AS id_pedido
FROM
    pedido
RIGHT JOIN
    cliente ON cliente.id = pedido.id_cliente;

Juntando Múltiplas Tabelas

E para ver os produtos de cada pedido? Precisamos juntar 3 tabelas: pedido, item_pedido (tabela de junção N:N) e produto.

SELECT
    pedido.id AS id_pedido,
    produto.nome AS produto_nome,
    item_pedido.quantidade,
    item_pedido.preco_unitario
FROM
    pedido
INNER JOIN
    item_pedido ON pedido.id = item_pedido.id_pedido
INNER JOIN
    produto ON item_pedido.id_produto = produto.id
WHERE
    pedido.id = 21; -- Para um pedido específico

Funções de Agregação

Elas operam em um conjunto de valores para retornar um único valor resumido.

• COUNT(): Conta o número de linhas.
• SUM(): Soma os valores de uma coluna numérica.
• AVG(): Calcula a média de uma coluna numérica.
• MIN(): Retorna o menor valor de uma coluna.
• MAX(): Retorna o maior valor de uma coluna.

Exemplos:

-- Quantos clientes temos?
SELECT COUNT(*) FROM cliente;

-- Qual o preço do produto mais caro?
SELECT MAX(preco) FROM produto;

Agrupando com GROUP BY

A cláusula GROUP BY é usada com funções de agregação para agrupar as linhas que têm os mesmos valores em colunas especificadas em um único registro de resumo.

Pergunta: Quantos pedidos cada cliente fez?

SELECT
    c.nome,
    COUNT(p.id) AS total_pedidos
FROM
    cliente AS c
LEFT JOIN
    pedido AS p ON c.id = p.id_cliente
GROUP BY
    c.id, c.nome; -- Agrupa por cliente

Tudo que está no SELECT e não é uma função de agregação, deve estar no GROUP BY.

Filtrando Grupos com HAVING

WHERE filtra linhas antes da agregação. HAVING filtra grupos depois da agregação.

Pergunta: Quais clientes fizeram mais de 2 pedidos?

SELECT
    c.nome,
    COUNT(p.id) AS total_pedidos
FROM
    cliente AS c
JOIN
    pedido AS p ON c.id = p.id_cliente
GROUP BY
    c.id, c.nome
HAVING
    COUNT(p.id) > 2; -- Filtra os grupos

Não poderíamos usar WHERE total_pedidos > 2 porque o WHERE é processado antes do COUNT ser calculado.

Resumo

• JOIN: A ferramenta para combinar dados de múltiplas tabelas.
  • INNER JOIN: O mais comum, retorna a interseção dos dados.
  • LEFT JOIN: Retorna tudo da tabela da esquerda, e o que combinar da direita.
  • RIGHT JOIN: O inverso do LEFT JOIN.
• Funções de Agregação: Para resumir e calcular dados.
  • COUNT(): Contar registros.
  • SUM(): Somar valores.
  • AVG(): Calcular a média.
  • MIN() e MAX(): Encontrar o menor e o maior valor.
• Agrupamento:
  • GROUP BY: Agrupar linhas para que as funções de agregação operem em cada grupo.
  • HAVING: Filtrar os resultados depois do agrupamento.

Dúvidas?

Exercícios

1. Clientes e Pedidos: Escreva uma consulta que liste o nome de cada cliente e a data de cada pedido que ele fez. Use INNER JOIN.
2. Todos os Produtos: Liste o nome de todos os produtos e a quantidade vendida em cada pedido. Inclua produtos que nunca foram vendidos (eles devem aparecer com quantidade NULL). Use LEFT JOIN.
3. Total de Vendas: Calcule o valor total de todos os pedidos. O valor de um pedido é a soma de (quantidade * preco_unitario) para cada item. Você precisará juntar item_pedido.

Exercícios

4. Relatório de Clientes: Crie um relatório que mostre o nome de cada cliente, o número total de pedidos que ele fez e o valor total que ele já gastou na loja. Ordene pelo valor total gasto, do maior para o menor.
5. Desafio com HAVING: Encontre os clientes que gastaram mais de R$ 500,00 no total.